/* =====================================================
   代码映射表（重新定义）
   表名：CodeTable
   说明：期货/股票合约基础信息，一条合约一条记录
   ===================================================== */

DROP TABLE IF EXISTS dbo.CodeTable;   -- SQL Server 2016+
GO

CREATE TABLE dbo.CodeTable
(
    CodeID            int              NOT NULL,
    Market            tinyint          NOT NULL,        -- 1=期货 2=股票 3=期权 ...
    MarketDesc        varchar(64)      NOT NULL,
    StockCode         varchar(64)      NOT NULL,        -- 交易所原始代码
    StockName         varchar(128)     NOT NULL,        -- 中文简称
    ExchangeType      smallint         NOT NULL,        -- 1=CFFEX 2=SHFE 3=DCE 4=CZCE 5=INE 6=GFEX
    ExchangeTypeDesc  varchar(64)          NULL,
    AddDate           int                  NULL,        -- yyyyMMdd
    CodeIDStr         varchar(64)          NULL,        -- 冗余，与CodeID相同，字符串
    StockCode2        varchar(64)          NULL,        -- 冗余，备用代码

    /* 主键 */
    CONSTRAINT PK_CodeTable PRIMARY KEY (CodeID),

    /* 唯一：交易所+原始代码 */
    CONSTRAINT UQ_CodeTable_Exchange_StockCode UNIQUE (ExchangeType, StockCode),


    /* 添加时间默认写入 */
    -- CONSTRAINT DF_CodeTable_AddDate DEFAULT (CONVERT(int,CONVERT(char(8),GETUTCDATE(),112))) FOR AddDate
) ON [PRIMARY];
GO

/* 索引 */
/* 1. 按市场拉取全部合约 */
CREATE NONCLUSTERED INDEX IX_CodeTable_Market
ON dbo.CodeTable (Market)
INCLUDE (StockCode, StockName);
GO

/* 2. 按交易所拉取 */
CREATE NONCLUSTERED INDEX IX_CodeTable_ExchangeType
ON dbo.CodeTable (ExchangeType)
INCLUDE (StockCode, StockName);
GO

/* 3. 代码模糊搜索 */
CREATE NONCLUSTERED INDEX IX_CodeTable_StockCode
ON dbo.CodeTable (StockCode)
INCLUDE (StockName, Market);
GO

/* 扩展属性：表注释 */
EXEC sys.sp_addextendedproperty
    @name  = N'MS_Description',
    @value = N'合约基础信息表，含期货、股票、期权等',
    @level0type = N'SCHEMA', @level0name = dbo,
    @level1type = N'TABLE',  @level1name = CodeTable;
GO

/* 字段注释 */
EXEC sys.sp_addextendedproperty
    @name  = N'MS_Description', @value = N'内部自增ID，主键',
    @level0type = N'SCHEMA', @level0name = dbo,
    @level1type = N'TABLE',  @level1name = CodeTable,
    @level2type = N'COLUMN', @level2name = CodeID;

EXEC sys.sp_addextendedproperty
    @name  = N'MS_Description', @value = N'市场类别：1=期货 2=股票 3=期权',
    @level0type = N'SCHEMA', @level0name = dbo,
    @level1type = N'TABLE',  @level1name = CodeTable,
    @level2type = N'COLUMN', @level2name = Market;

EXEC sys.sp_addextendedproperty
    @name  = N'MS_Description', @value = N'交易所类型：1=CFFEX 2=SHFE 3=DCE 4=CZCE 5=INE 6=GFEX',
    @level0type = N'SCHEMA', @level0name = dbo,
    @level1type = N'TABLE',  @level1name = CodeTable,
    @level2type = N'COLUMN', @level2name = ExchangeType;
GO